﻿using System;
using System.Data;
using BugManager;

namespace DAL
{
    public class StoredProcedures
    {

        private MainDB _db;

        public StoredProcedures(MainDB db)
        {
            _db = db;
        }

        public MainDB Database
        {
            get { return _db; }
        }

        protected DataSet CreateDataSet(IDbCommand command)
        {
            return _db.CreateDataSet(command);
        }

        protected DataTable CreateDataTable(IDbCommand command)
        {
            try
            {
                return _db.CreateDataTable(command);
            }
            catch (Exception ex)
            {
                ErrorManager.Log(ex);
                return null;

            }
        }


        #region For sitemap generating
        public DataTable GetNewsByTime(DateTime startDate, DateTime endDate)
        {
            IDbCommand cmd = _db.CreateCommand("gianght_GetNewsByTime", true);
            _db.AddParameter(cmd, "startDate", DbType.String, startDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            _db.AddParameter(cmd, "endDate", DbType.String, endDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            return CreateDataTable(cmd);
        }

        public DataTable GetVideoByTime(DateTime startDate, DateTime endDate)
        {
            IDbCommand cmd = _db.CreateCommand("gianght_video_GetVideoByTime", true);
            _db.AddParameter(cmd, "startDate", DbType.String, startDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            _db.AddParameter(cmd, "endDate", DbType.String, endDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            return CreateDataTable(cmd);
        } 
        #endregion


        /// <summary>
        /// Lấy dữ liệu bên MSoha
        /// </summary>
        /// <param name="top">top tin cần lấy</param>
        /// <returns>datatable</returns>
        public DataTable GetCareableNews(int top)
        {
            var cmd = _db.CreateCommand("gianght_GetCareableNews", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            return CreateDataTable(cmd);
        }

        public DataTable GetNewsByPosition(int top, int typeId, int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetNewsByPosition", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "typeId", DbType.Int32, typeId);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return CreateDataTable(cmd);
        }

        public DataTable GetNewsDetail(long newsId)
        {
            var cmd = _db.CreateCommand("giang_News_GetNewsDetail", true);
            _db.AddParameter(cmd, "newsId", DbType.Int64, newsId);
            return CreateDataTable(cmd);
        }

        public DataTable GetNewsMetaData(long newsId)
        {
            var cmd = _db.CreateCommand("giang_News_GetNewsMetaData", true);
            _db.AddParameter(cmd, "newsId", DbType.Int64, newsId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetListNews(int pageIndex, int pageSize, int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetListNews", true);
            _db.AddParameter(cmd, "pageIndex", DbType.Int32, pageIndex);
            _db.AddParameter(cmd, "pageSize", DbType.Int32, pageSize);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return CreateDataTable(cmd);
        }

        public DataTable GetListNewsWithSubCategory(int pageIndex, int pageSize, int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetListNewsWithSubCategory", true);
            _db.AddParameter(cmd, "pageIndex", DbType.Int32, pageIndex);
            _db.AddParameter(cmd, "pageSize", DbType.Int32, pageSize);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return CreateDataTable(cmd);
        }

        public DataTable CountNews(int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_CountNews", true);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return CreateDataTable(cmd);
        }

        public DataTable GetEmbedNewsOnPage(int top, int zoneId, int type)
        {
            var cmd = _db.CreateCommand("giang_News_GetEmbedNewsOnPage", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            _db.AddParameter(cmd, "type", DbType.Int16, type);
            return CreateDataTable(cmd);
        }

        public DataTable GetTagDetail(string tagUrl)
        {
            var cmd = _db.CreateCommand("giang_News_GetTagByTagUrl", true);
            _db.AddParameter(cmd, "tagUrl", DbType.String, tagUrl);
            return CreateDataTable(cmd);
        }

        public DataTable GetAllCategories()
        {
            var cmd = _db.CreateCommand("giang_News_GetAllCategories", true);
            return CreateDataTable(cmd);
        }

        public DataTable GetOlderNews(int top, int zoneId, long newsId, DateTime publishedDate)
        {
            var cmd = _db.CreateCommand("giang_News_GetOlderNews", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            _db.AddParameter(cmd, "newsId", DbType.Int64, newsId);
            _db.AddParameter(cmd, "publishedDate", DbType.DateTime, publishedDate);
            return CreateDataTable(cmd);
        }

        public DataTable GetHotNews(int top, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetHotNews", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return CreateDataTable(cmd);
        }

        public DataTable GetTagDetail(int tagId)
        {

            var cmd = _db.CreateCommand("giang_News_GetTagDetail", true);
            _db.AddParameter(cmd, "tagId", DbType.Int32, tagId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetNewsByTag(int tagId, int pageIndex, int pageSize)
        {
            var cmd = _db.CreateCommand("giang_News_GetNewsByTag", true);
            _db.AddParameter(cmd, "tagId", DbType.Int32, tagId);
            _db.AddParameter(cmd, "pageIndex", DbType.Int32, pageIndex);
            _db.AddParameter(cmd, "pageSize", DbType.Int32, pageSize);
            return _db.CreateDataTable(cmd);
        }

        public DataTable CountNewsByTag(int tagId)
        {
            var cmd = _db.CreateCommand("giang_News_CountNewsByTag", true);
            _db.AddParameter(cmd, "tagId", DbType.Int32, tagId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable CountNewsByDate(int day, int month, int year, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_CountNewsByDate", true);
            _db.AddParameter(cmd, "day", DbType.Int32, day);
            _db.AddParameter(cmd, "month", DbType.Int32, month);
            _db.AddParameter(cmd, "year", DbType.Int32, year);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetNewsByDate(int day, int month, int year, int pageIndex, int pageSize, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetNewsByDate", true);
            _db.AddParameter(cmd, "day", DbType.Int32, day);
            _db.AddParameter(cmd, "month", DbType.Int32, month);
            _db.AddParameter(cmd, "year", DbType.Int32, year);
            _db.AddParameter(cmd, "pageIndex", DbType.Int32, pageIndex);
            _db.AddParameter(cmd, "pageSize", DbType.Int32, pageSize);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        /// <summary>
        /// lấy dữ liệu cho box nhúng trên các site kenh14, genk, gamek...
        /// </summary>
        /// <param name="top">top tin cần lấy</param>
        /// <param name="type">type id tương ứng với site đặt box nhúng</param>
        /// <param name="zoneId">id của chuyên mục</param>
        /// <returns>datatable</returns>
        public DataTable GetEmbedNews(int top, int type, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetEmbedNews", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "type", DbType.Int32, type);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return CreateDataTable(cmd);
        }

        public DataTable GetLiveNews(long newsId)
        {
            var cmd = _db.CreateCommand("giang_News_GetLiveNews", true);
            _db.AddParameter(cmd, "newsId", DbType.Int64, newsId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetSportNewsByPosition(int top, int typeId, int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetSportNewsByPosition", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "typeId", DbType.Int32, typeId);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetMostViewNews(int top, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetMostViewNews", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetEmbedGroup()
        {
            var cmd = _db.CreateCommand("giang_News_GetEmbedGroup", true);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetEmbedThreads(int top, int zoneId, int type)
        {
            var cmd = _db.CreateCommand("giang_News_GetEmbedThreads", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            _db.AddParameter(cmd, "type", DbType.Int32, type);
            return _db.CreateDataTable(cmd);
        }

        #region SEO
        public DataTable SEO_GetCategoryDetail(int zoneId)
        {
            var cmd = _db.CreateCommand("giang_SEO_GetZoneMetaData", true);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable SEO_GetNewsDetail(long newsId)
        {
            var cmd = _db.CreateCommand("giang_SEO_GetNewsMetaData", true);
            _db.AddParameter(cmd, "newsId", DbType.Int64, newsId);
            return _db.CreateDataTable(cmd);
        }
        #endregion

        #region Video
        public DataTable GetVideoByMode(int top, int mode, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_Video_GetVideoByMode", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "mode", DbType.Int32, mode);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetNewestVideo(int top, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_Video_GetNewestVideo", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetAllVideoZones()
        {
            var cmd = _db.CreateCommand("giang_Video_GetAllVideoZones", true);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetListVideo(int pageIndex, int pageSize, int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_Video_GetListVideo", true);
            _db.AddParameter(cmd, "pageIndex", DbType.Int32, pageIndex);
            _db.AddParameter(cmd, "pageSize", DbType.Int32, pageSize);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetMostViewVideo(int pageIndex, int pageSize, int topExclusion, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_Video_GetMostViewVideo", true);
            _db.AddParameter(cmd, "pageIndex", DbType.Int32, pageIndex);
            _db.AddParameter(cmd, "pageSize", DbType.Int32, pageSize);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable CountVideoByZone(int zoneId, int topExclusion)
        {
            var cmd = _db.CreateCommand("giang_Video_CountVideoByZone", true);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            _db.AddParameter(cmd, "topExclusion", DbType.Int32, topExclusion);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetVideoDetail(int videoId)
        {
            var cmd = _db.CreateCommand("giang_Video_GetVideoDetail", true);
            _db.AddParameter(cmd, "videoId", DbType.Int32, videoId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetEmbedVideo(int top, int type, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_Video_GetEmbedVideo", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "type", DbType.Int32, type);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }
        #endregion

        #region Sport Crawler
        public DataTable GetLichThiDau()
        {
            IDbCommand cmd = _db.CreateCommand("gianght_GetLichThiDau", true);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetBangXepHang()
        {
            IDbCommand cmd = _db.CreateCommand("gianght_GetBangXepHang", true);
            return _db.CreateDataTable(cmd);
        }
        #endregion

        #region Vote
        public DataTable GetVoteAnswers(int voteId)
        {
            var txtCmd = string.Format("select Id, Value, VoteRate from VoteAnswers WHERE VoteId={0}", voteId);
            IDbCommand cmd = _db.CreateCommand(txtCmd, false);
            return CreateDataTable(cmd);
        }

        public DataTable GetVote(int voteId)
        {
            var cmd = _db.CreateCommand("select Id, Title, Avatar, Sapo, StartedDate, EndedDate from Vote", false);
            return CreateDataTable(cmd);
        }

        public DataTable Vote(int voteId, int answerId)
        {
            var cmd = _db.CreateCommand("gianght_Vote", true);
            _db.AddParameter(cmd, "voteId", DbType.Int32, voteId);
            _db.AddParameter(cmd, "answerId", DbType.Int32, answerId);
            return _db.CreateDataTable(cmd);
        }
        #endregion

        #region Photo
        public DataTable GetNewsByType(int top, int type, int zoneId)
        {
            var cmd = _db.CreateCommand("giang_News_GetNewsByType", true);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            _db.AddParameter(cmd, "type", DbType.Int16, type);
            _db.AddParameter(cmd, "zoneId", DbType.Int32, zoneId);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetPhotosInNews(long newsId, int top)
        {
            var cmd = _db.CreateCommand("giang_Photo_GetPhotosInNews", true);
            _db.AddParameter(cmd, "newsId", DbType.Int64, newsId);
            _db.AddParameter(cmd, "top", DbType.Int32, top);
            return _db.CreateDataTable(cmd);
        }
        #endregion

        public DataTable GetAllTags()
        {
            var cmd = _db.CreateCommand("select * from tag", false);            
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetBaiGiaoLuuTrucTuyen(int newsId)
        {
            var cmd = _db.CreateCommand(string.Format("select top 1 title, summary, content, [status] from interview where id = '{0}'", newsId), false);
            return _db.CreateDataTable(cmd);
        }

        public DataTable GetInterviewId(long newsId)
        {
            var cmd = _db.CreateCommand(string.Format("select top 1 configvalue from newsconfig where ConfigGroup = 'OnlineConference' and configname = '{0}'", newsId), false);
            return _db.CreateDataTable(cmd);
        }
    }
}
